﻿using System;
using System.Data;
using System.Data.Common;
using System.Text;
using Besture.Common;
using Besture.Model;

namespace Besture.Dal
{
    public partial class DictionaryTypeDal : BaseDataAccess
    {

        /// <summary>
        /// 获取详细信息
        /// </summary>
        /// <param name="id">主键值</param>
        /// <returns></returns>
        public DictionaryTypeModel GetModel(int id)
        {
            try
            {
                StringBuilder selectSql = new StringBuilder();
                selectSql.Append("SELECT * FROM TBDictionaryType");
                selectSql.Append(" WHERE ");
                selectSql.Append(" ID=@ID ");
                DbCommand ldcDBCommand = base.GetCommand(selectSql.ToString());
                base.SetParameter(ldcDBCommand, "@ID", DbType.Int32, id);
                DataTable dt = base.GetDataSet(ldcDBCommand).Tables[0];
                if (dt.Rows.Count > 0)
                    return ModelConvertHelper<DictionaryTypeModel>.ConvertToModelList(dt)[0];
                else return null;
            }
            catch (Exception exception)
            {
                WriteLog(exception.ToString());
                return null;
            }
        }

        /// <summary>
        /// 获取全部数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetDataTable()
        {
            try
            {
                StringBuilder selectSql = new StringBuilder();
                selectSql.Append("SELECT * FROM TBDictionaryType ORDER BY SortID DESC ");
                DbCommand ldcDBCommand = base.GetCommand(selectSql.ToString());
                DataTable dt = base.GetDataSet(ldcDBCommand).Tables[0];
                return dt;
            }
            catch (Exception exception)
            {
                WriteLog(exception.ToString());
                return null;
            }
        }

        /// <summary>
        /// 根据类型读取数据
        /// </summary>
        /// <param name="type">类型</param>
        /// <returns></returns>
        public DataTable GetDataTableByType(int type)
        {
            try
            {
                StringBuilder selectSql = new StringBuilder();
                selectSql.Append(string.Format(@"
                SELECT * FROM dbo.TBDictionaryType
                WHERE 
                IsDeleted={0} AND 
                Type={1}
                ORDER BY Code ASC ", Constants.ISDELETED_NO, type));
                DbCommand ldcDBCommand = base.GetCommand(selectSql.ToString());
                DataTable dt = base.GetDataSet(ldcDBCommand).Tables[0];
                return dt;
            }
            catch (Exception exception)
            {
                WriteLog(exception.ToString());
                return null;
            }
        }



        public DictionaryTypeModel GetDataTableByTypeAndCode(int type, int iCode)
        {
            try
            {
                StringBuilder selectSql = new StringBuilder();
                selectSql.Append(string.Format(@"
                SELECT * FROM dbo.TBDictionaryType
                WHERE 
                IsDeleted={0} AND Type={1} AND Code={2}
                ORDER BY Code ASC ", Constants.ISDELETED_NO, type, iCode));
                DbCommand ldcDBCommand = base.GetCommand(selectSql.ToString());
                DataTable dt = base.GetDataSet(ldcDBCommand).Tables[0];
                if (dt.Rows.Count > 0)
                    return ModelConvertHelper<DictionaryTypeModel>.ConvertToModelList(dt)[0];
                else return null;
            }
            catch (Exception exception)
            {
                WriteLog(exception.ToString());
                return null;
            }
        }



        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(DictionaryTypeModel model)
        {
            var identityId = -1;
            try
            {

				
            string sqlString = string.Format(@"
                INSERT INTO TBDictionaryType
                (
                        Code
                        ,Name
                        ,Type
                        ,Comment
                        ,IsDeleted
                        ,CreateBy
                        ,CreateTime
                        ,UpdateBy
                        ,UpdateTime
                        ,SortID
                )
                VALUES
                (
                        @Code
                       ,@Name
                       ,@Type
                       ,@Comment
                       ,@IsDeleted
                       ,@CreateBy
                       ,@CreateTime
                       ,@UpdateBy
                       ,@UpdateTime
                       ,@SortID
                )SELECT @@IDENTITY");

            DbCommand ldcDBCommand = base.GetCommand(sqlString);

            //参数设定
            SetParameter(ldcDBCommand, "@Code", DbType.String, model.Code);
            SetParameter(ldcDBCommand, "@Name", DbType.String, model.Name);
            SetParameter(ldcDBCommand, "@Type", DbType.Int32, model.Type);
            SetParameter(ldcDBCommand, "@Comment", DbType.String, model.Comment);
            SetParameter(ldcDBCommand, "@IsDeleted", DbType.Int32, model.IsDeleted);
            SetParameter(ldcDBCommand, "@CreateBy", DbType.String, model.CreateBy);
            SetParameter(ldcDBCommand, "@CreateTime", DbType.DateTime, model.CreateTime);
            SetParameter(ldcDBCommand, "@UpdateBy", DbType.String, model.UpdateBy);
            SetParameter(ldcDBCommand, "@UpdateTime", DbType.DateTime, model.UpdateTime);
            SetParameter(ldcDBCommand, "@SortID", DbType.Int32, model.SortID);



                identityId = base.ExecuteScalarInsert(ldcDBCommand);
            }
            catch (Exception exception)
            {
                WriteLog(exception.ToString());
            }
            return identityId;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Update(DictionaryTypeModel model)
        {
            int iTmp = -1;
            try
            {
				
            string sqlString = string.Format(@"
                UPDATE TBDictionaryType
                SET
                    Code = @Code
                    ,Name = @Name
                    ,Type = @Type
                    ,Comment = @Comment
                    ,IsDeleted = @IsDeleted
                    ,UpdateBy = @UpdateBy
                    ,UpdateTime = @UpdateTime
                    ,SortID=@SortID
                WHERE
                    ID = @ID
            ");

            DbCommand ldcDBCommand = base.GetCommand(sqlString);
            //绑定SQL参数
            SetParameter(ldcDBCommand, "@ID", DbType.Int32, model.ID);
            SetParameter(ldcDBCommand, "@Code", DbType.String, model.Code);
            SetParameter(ldcDBCommand, "@Name", DbType.String, model.Name);
            SetParameter(ldcDBCommand, "@Type", DbType.Int32, model.Type);
            SetParameter(ldcDBCommand, "@Comment", DbType.String, model.Comment);
            SetParameter(ldcDBCommand, "@IsDeleted", DbType.Int32, model.IsDeleted);
            SetParameter(ldcDBCommand, "@UpdateBy", DbType.String, model.UpdateBy);
            SetParameter(ldcDBCommand, "@UpdateTime", DbType.DateTime, model.UpdateTime);
            SetParameter(ldcDBCommand, "@SortID", DbType.Int32, model.SortID);


                iTmp = base.ExecuteNonQuery(ldcDBCommand);
            }
            catch (Exception exception)
            {
                WriteLog(exception.ToString());
            }
            return iTmp;
        }

        /// <summary>
        /// 单个删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Delete(int id)
        {
            int iTmp = -1;
            try
            {
                StringBuilder updateSql = new StringBuilder();
                updateSql.Append("DELETE FROM TBDictionaryType");
                updateSql.Append(" WHERE ");
                updateSql.Append(" ID=@ID ");
                DbCommand ldcDBCommand = base.GetCommand(updateSql.ToString());
                base.SetParameter(ldcDBCommand, "@ID", DbType.Int32, id);
                iTmp = base.ExecuteNonQuery(ldcDBCommand);
            }
            catch (Exception exception)
            {
                WriteLog(exception.ToString());
            }
            return iTmp;
        }

        /// <summary>
        /// 批量删除
        /// </summary>
        /// <param name="idList"></param>
        /// <returns></returns>
        public int DeleteList(string ids)
        {
            int iTmp = -1;
            try
            {
                StringBuilder updateSql = new StringBuilder();
                updateSql.Append("exec('DELETE FROM TBDictionaryType");
                updateSql.Append(" WHERE ");
                updateSql.Append(" ID in ('+@ID+') ')");
                DbCommand ldcDBCommand = base.GetCommand(updateSql.ToString());
                base.SetParameter(ldcDBCommand, "@ID", DbType.String, ids);
                iTmp = base.ExecuteNonQuery(ldcDBCommand);
            }
            catch (Exception exception)
            {
                WriteLog(exception.ToString());
            }
            return iTmp;
        }
    }
}

